
package hk.ces.oxfam.patch.patchs;

import hk.ces.oxfam.patch.DBPatch;
import hk.ces.oxfam.patch.db.result.ExcuteResult;
import hk.ces.oxfam.patch.db.sql.PreparedSQL;
import hk.ces.oxfam.patch.db.result.SQLResult;
import hk.ces.oxfam.patch.db.sql.SQLUtil;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.log4j.Logger;

/**
 *
 * @author ReutyCheng
 */
public class BlacklistEventPatch implements DBPatch
{
  public String getName()
  {
    return "Patch Blacklist Event. From event year to Event Id";
  }

  public void patch(Connection connection) throws SQLException
  {
    List<Integer> eventYearList = this.getDistinctEventYearInBlacklist(connection);
    Map<Integer, Integer> eventYearMapToEventId = this.getEventIdByBlacklistEventYear(connection);
    
    int resultCount = 0;
    for (Integer eventYear : eventYearList)
    {
      resultCount = this.updateBlacklistEvent(connection, eventYear, eventYearMapToEventId.get(eventYear));
    }
    
    Logger.getLogger(this.getClass()).debug("Totally update Blacklist[count=" + resultCount + "]");
  }

  private int updateBlacklistEvent(Connection connection, int eventYear, int eventId) throws SQLException
  {
    String sql = "update blacklist set event_id = ? where eventyear = ?";
    Object[] params = new Object[]{eventId, eventYear};
    ExcuteResult result = SQLUtil.execute(connection, new PreparedSQL(sql, params));

    if (result.getResultCount() == 1)
    {
      Logger.getLogger(this.getClass()).debug(result.getExcuteStatus() + " : Update Blacklist with eventYear[" + eventYear + "], set Event_ID[" + eventId + "] Success [count=" + result.getResultCount() + "]!");
    }
    else
    {
      Logger.getLogger(this.getClass()).error(result.getExcuteStatus() + " : Update Blacklist with eventYear[" + eventYear + "], set Event_ID[" + eventId + "] Fail [count=" + result.getResultCount() + "]!");
    }

    return result.getResultCount();
  }

  private List<Integer> getDistinctEventYearInBlacklist(Connection connection) throws SQLException
  {
    String sql = "select distinct eventyear from blacklist";
    Object[] params = new Object[]{};
    List<Integer> result = SQLUtil.certainColumn(connection, new PreparedSQL(sql, params), Integer.class);

    return result;
  }

  private Map<Integer, Integer> getEventIdByBlacklistEventYear(Connection connection) throws SQLException
  {
    String sql = "select id, eventyear from otwevent where eventyear in (select distinct eventyear from blacklist) order by id asc";
    Object[] params = new Object[]{};
    SQLResult result = SQLUtil.query(connection, new PreparedSQL(sql, params));

    Map<Integer, Integer> eventYearMapToEventId = new HashMap<Integer, Integer>(result.getRowCount());
    List<Object[]> rows = result.getRows();
    for (Object[] columnsInRow : rows)
    {
      eventYearMapToEventId.put((Integer) columnsInRow[1], (Integer) columnsInRow[0]);
    }

    return eventYearMapToEventId;
  }
}
